﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using FastReport;
using FastReport.Utils;
using HYI.SDBSERVER;
using Sunny.UI;
using OfficeOpenXml;

namespace SDBSERVER
{
    public partial class FMSINGLE : Form
    {
        public string sUser;
        public string sMenu;
        public string sPrintKey; //打印数据
        public string _INPUTTABLE = string.Empty; //导入表名
        public int rowindex=-1;
        private Report FReport;

        /// <summary>
        /// 每页记录数
        /// </summary>
        public int pageSize = 15;

        /// <summary>
        /// 总记录数
        /// </summary>
        public int recordCount = 0;

        /// <summary>
        /// 总页数
        /// </summary>
        public int pageCount = 0;

        /// <summary>
        /// 当前页
        /// </summary>
        public int currentPage = 0;

        public DataTable Qrytable = new DataTable();

        /// <summary>
        /// 分页的方法
        /// </summary>
        /// <param name="str"></param>
        private void PageSorter()
        {
            //创建虚拟表
            recordCount = Qrytable.Rows.Count;     //记录总行数
            pageCount = (recordCount / pageSize);
            if ((recordCount % pageSize) > 0)
            {
                pageCount++;
            }

            //默认第一页
            currentPage = 1;

            LoadPage();//调用加载数据的方法
        }

        /// <summary>
        /// LoadPage方法
        /// </summary>
        private void LoadPage()
        {
            if (currentPage < 1) currentPage = 1;
            if (currentPage > pageCount) currentPage = pageCount;
            try
            {
                int beginRecord;    //开始指针
                int endRecord;      //结束指针
                DataTable dtTemp;
                dtTemp = Qrytable.Clone();

                beginRecord = pageSize * (currentPage - 1);
                if (currentPage == 1) beginRecord = 0;
                endRecord = pageSize * currentPage;

                if (currentPage == pageCount) endRecord = recordCount;
                for (int i = beginRecord; i < endRecord; i++)
                {
                    dtTemp.ImportRow(Qrytable.Rows[i]);
                }
                uiDGView.Rows.Clear();
                for (int i = 0; i < dtTemp.Rows.Count; i++)
                {
                    //dataGridView1.Rows.Add(new object[] { dtTemp.Rows[i][0], dtTemp.Rows[i][1], dtTemp.Rows[i][2] });
                    int index = this.uiDGView.Rows.Add();
                    for (int j = 0; j < dtTemp.Columns.Count; j++)
                    {
                        this.uiDGView.Rows[index].Cells[j].Value = dtTemp.Rows[i][j];
                    }
                }
                labPageIndex.Text = "" + currentPage.ToString() + "/" + pageCount.ToString();//当前页
                                                                                             //labRecordCount.Text = "总行数: " + recordCount.ToString() + " 行";//总记录数

                uiDGView.AllowUserToAddRows = false;//關閉dataGridview中最後一個空白行。
            }
            catch (Exception ex)
            {
                UIMessageTip.ShowOk("显示错误,退出重新进入");
                Console.WriteLine("显示错误:" + ex.Message);
                //Log.doWriteLog(1, "数据库连接失败2:" + ex.Message);
            }
        }

        /// <summary>
        /// 创建表格列
        /// </summary>
        private void doCreateColumn()
        {
            //DBSERVER myADO = new DBSERVER();
            //DataTable myTable = myADO.query(SQL);
            if (uiDGView.Columns.Count > 0)
            {
                return;
            }

            if(Qrytable is null)
            {
                return;
            }

            for (int i = 0; i < Qrytable.Columns.Count; i++)
            {
                string dgvColName = Qrytable.Columns[i].ToString();
                string dgvColHeaderText = Qrytable.Columns[i].ToString();
                Type dataType = Qrytable.Columns[i].DataType;
                //Console.WriteLine(dataType.ToString());
                DataGridViewColumn dgvcolumn = new DataGridViewTextBoxColumn();
                if (dataType.Name == "String")
                {
                    dgvcolumn.ValueType = typeof(string);
                }
                else if (dataType.Name == "Int32")
                {
                    dgvcolumn.ValueType = typeof(int);
                }
                else if (dataType.Name == "DateTime")
                {
                    dgvcolumn.ValueType = typeof(string);
                }
                else 
                {
                    dgvcolumn.ValueType = typeof(string);
                }
                dgvcolumn.Name = dgvColName;                        //列名
                dgvcolumn.HeaderText = dgvColHeaderText;  //列头名
                uiDGView.Columns.Add(dgvcolumn); //增加列
            }
            //myADO.Dispose();
        }

        /// <summary>
        /// 首页
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnFirst_Click(object sender, EventArgs e)
        {
            if (currentPage == 1)
            { return; }
            currentPage = 1;
            LoadPage();
        }
        /// <summary>
        /// 上一页
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnPrev_Click(object sender, EventArgs e)
        {
            if (currentPage == 1)
            { return; }
            currentPage--;
            LoadPage();
        }
        /// <summary>
        /// 下一页
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnNext_Click(object sender, EventArgs e)
        {
            if (currentPage == pageCount)
            { return; }
            currentPage++;
            LoadPage();
        }
        /// <summary>
        /// 尾页
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnLast_Click(object sender, EventArgs e)
        {
            if (currentPage == pageCount)
            { return; }
            currentPage = pageCount;
            LoadPage();
        }

        //刷新版本
        protected virtual void doUpCache(string sTableName)
        {
            DBSERVER myADO = new DBSERVER();
            string SQL = "update TABLEVERSION set VERSION=VERSION+1 where TABLENAME='"+ sTableName + "'";
            myADO.doexec(SQL);
            myADO.Dispose();

            DBCache myCache = new DBCache();
            myCache.doCache();
        }

        //文本框不为空检查
        protected virtual bool CheckEmpty(UITextBox edit, string desc)
        {
            bool result = edit.Text.IsValid();
            if (!result)
            {
                this.ShowWarningDialog(desc);
                edit.Focus();
            }

            return result;
        }

        //下拉框为空检查
        protected bool CheckEmpty(UIComboBox edit, string desc)
        {
            bool result = edit.Text.IsValid();
            if (!result)
            {
                this.ShowWarningDialog(desc);
                edit.Focus();
            }

            return result;
        }

        //数字框范围检查
        protected bool CheckRange(UITextBox edit, int min, int max, string desc)
        {
            bool result = edit.IntValue >= min && edit.IntValue <= max;
            if (!result)
            {
                this.ShowWarningDialog(desc);
                edit.Focus();
            }

            return result;
        }

        //新增实现代码
        protected virtual void OnSys01()
        {
            doSetButton(0);
            uiTabForm.SelectedTab = uiTabForm.TabPages[1];
        }

        //编辑实现代码
        protected virtual void OnSys02()
        {
            doSetButton(0);
            uiTabForm.SelectedTab = uiTabForm.TabPages[1];
        }

        //删除实现代码
        protected virtual void OnSys03()
        {
            
        }

        //查询实现代码
        protected virtual void OnSys04()
        {
            rowindex = -1;
            doCreateColumn();

            uiDGView.Rows.Clear();
            if (Qrytable is null || Qrytable.Rows.Count == 0)
            {
                Console.WriteLine("0");
                labPageIndex.Text = "0/0";
                btnFirst.Enabled = false;
                btnPrev.Enabled = false;
                btnNext.Enabled = false;
                btnLast.Enabled = false;
                return;
            }

            btnFirst.Enabled = true;
            btnPrev.Enabled = true;
            btnNext.Enabled = true;
            btnLast.Enabled = true;

            PageSorter();//分页 
        }

        //导入实现代码
        protected virtual void OnSys05()
        {
            doInputExecl(_INPUTTABLE);
        }

        //导入
        public void doInputExecl(string sTable)
        {
            FMINPUT myINPUT = new FMINPUT();
            myINPUT.uiTitle.Text = this.Text + "->导入数据";
            myINPUT._INPUTTABLE = sTable;
            myINPUT._USERNO = sUser;
            myINPUT.ShowDialog();
            myINPUT.Dispose();
        }

        private void doExcel()
        {
            string path = System.Environment.CurrentDirectory + "\\out";
            if (!Directory.Exists(path))//判断是否存在
            {
                Directory.CreateDirectory(path);//创建新路径
            }
            string sExcelFile = path + "\\" + this.Text + ".xlsx";
            if (File.Exists(sExcelFile))
            {
                File.Delete(sExcelFile);
            }

            using (ExcelPackage p = new ExcelPackage(new FileInfo(sExcelFile)))
            {
                //删除隐藏列
                DataTable ExcelTable = Qrytable.Copy();

                for (int i = ExcelTable.Columns.Count-1; i>=0; i--)
                {
                    string sField = ExcelTable.Columns[i].ColumnName;
                    for (int j = 0; j < uiDGView.Columns.Count; j++)
                    {
                        if (sField.Equals(uiDGView.Columns[j].Name))
                        {
                            ExcelTable.Columns[i].Caption = uiDGView.Columns[j].HeaderText;
                            if (!uiDGView.Columns[j].Visible)
                            {
                                ExcelTable.Columns.Remove(sField);
                            }
                            break;
                        }
                    }
                }
                //
                int dd = 1000000;
                int pages = (int)ExcelTable.Rows.Count / dd + 1;
                if (ExcelTable.Rows.Count % dd == 0)
                    pages = pages - 1;

                for (int k = 1; k <= pages; k++)
                {
                    var ws = p.Workbook.Worksheets.Add(ExcelTable.TableName + k.ToString());
                    ws.Cells["A1"].LoadFromDataTable(ExcelTable, true);
                }
                p.Save();
                p.Dispose();
                UIMessageTip.ShowOk("导出成功");
            }
        }

        //导出实现代码
        protected virtual void OnSys06()
        {
            try
            {
                if (Qrytable is null || Qrytable.Rows.Count == 0)
                {
                    UIMessageTip.ShowOk("请先查询数据");
                    return;
                }

                //1.读取导出语句
                string SQL = "select RL_EXCEL_SQL,RL_DATA2_SQL from REPORT_LIST where RL_REPORT_ID = '" + sMenu + "'";
                DBSERVER myADO = new DBSERVER();
                DataTable dt = myADO.query(SQL);
                if (dt is null || dt.Rows.Count == 0)
                {
                    myADO.Dispose();
                    //UIMessageTip.ShowOk("请先设定导出语句");

                    //直接导出当前数据集
                    doExcel();

                    return;
                }
                string sKeyField = dt.Rows[0]["RL_DATA2_SQL"].ToString();
                string sExcelSQL = dt.Rows[0]["RL_EXCEL_SQL"].ToString();

                //2.取数据
                string sPrintKey = "('0'";

                for (int i = 0; i < Qrytable.Rows.Count; i++)
                {
                    string sKey = Qrytable.Rows[i][sKeyField].ToString();

                    if (sPrintKey.IndexOf(sKey) == -1)
                    {
                        sPrintKey = sPrintKey + ",'" + sKey + "'";
                    }
                }
                sPrintKey = sPrintKey + ")";
                SQL = sExcelSQL.Replace(":fbillno", sPrintKey);
                Console.WriteLine(SQL);
                dt = myADO.query(SQL);
                if (dt is null || dt.Rows.Count == 0)
                {
                    myADO.Dispose();
                    UIMessageTip.ShowOk("查询不到数据");
                    return;
                }

                //3.导出
                string path = System.Environment.CurrentDirectory + "\\out";
                if (!Directory.Exists(path))//判断是否存在
                {
                    Directory.CreateDirectory(path);//创建新路径
                }
                string sExcelFile = path + "\\" + this.Text + ".xlsx";
                if (File.Exists(sExcelFile))
                {
                    File.Delete(sExcelFile);
                }

                using (ExcelPackage p = new ExcelPackage(new FileInfo(sExcelFile)))
                {
                    int dd = 1000000;
                    int pages = (int)dt.Rows.Count / dd + 1;
                    if (dt.Rows.Count % dd == 0)
                        pages = pages - 1;

                    for (int k = 1; k <= pages; k++)
                    {
                        var ws = p.Workbook.Worksheets.Add(dt.TableName + k.ToString());
                        ws.Cells["A1"].LoadFromDataTable(dt, true);
                    }
                    p.Save();
                    p.Dispose();
                    UIMessageTip.ShowOk("导出成功");
                    myADO.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("导出报错:" + ex.Message);
            }
        }


        //打印实现代码
        protected virtual void OnSys07()
        {
            string SQL = "select RL_FILENAME,RL_TITLE_SQL, RL_DATA_SQL from REPORT_LIST where RL_REPORT_ID = '" + sMenu+"'";
            DBSERVER myADO = new DBSERVER();
            DataTable dt = myADO.query(SQL);
            if(dt is null || dt.Rows.Count == 0)
            {
                myADO.Dispose();
                return;
            }
            DataSet FDataSet=new DataSet();

            string sTitle = dt.Rows[0]["RL_TITLE_SQL"].ToString();
            string sDetail = dt.Rows[0]["RL_DATA_SQL"].ToString();
            string sReportFile = dt.Rows[0]["RL_FILENAME"].ToString();

            DataTable dtHead = new DataTable();
            if (sTitle.Length > 0)
            {
                sTitle = sTitle.Replace(":fbillno", sPrintKey);
                Console.WriteLine(sTitle);
                dtHead = myADO.query(sTitle);
            }

            DataTable dtDetail = new DataTable();
            if (sDetail.Length > 0)
            {
                sDetail = sDetail.Replace(":fbillno", sPrintKey);
                Console.WriteLine(sDetail);
                dtDetail = myADO.query(sDetail);
            }

            dtHead.TableName = "myHead";
            FDataSet.Tables.Add(dtHead.Copy());
            dtDetail.TableName = "myDetail";
            FDataSet.Tables.Add(dtDetail.Copy());

            string FReportsFolder = System.Environment.CurrentDirectory + "\\Reports\\" + sReportFile;
            if (sUser.ToLower() != "888888")
            {
                if (!File.Exists(FReportsFolder))
                {
                    this.ShowWarningDialog("报表文件("+ sReportFile + ")不存在");
                    return;
                }
            }

            FReport = new Report();
            FReport.RegisterData(FDataSet, "SUI");
            if (File.Exists(FReportsFolder)) {
                FReport.Load(FReportsFolder);
            }

            if (sUser.ToLower() != "888888") { 
                FReport.Show();  //报表文件存在就直接预览
            }
            else
            {
                FReport.Design();
            }

            myADO.Dispose();
            FReport.Dispose();

        }

        //保存实现代码
        protected virtual void OnSys08()
        {
            doSetButton(1);
            uiTabForm.SelectedTab = uiTabForm.TabPages[0];
        }

        //取消实现代码
        protected virtual void OnSys09()
        {
            doSetButton(1);
            uiTabForm.SelectedTab = uiTabForm.TabPages[0];
        }

        //执行浏览代码
        protected virtual void OnSys10()
        {
            doSetButton(0);
            sys08.Visible = false;
            uiTabForm.SelectedTab = uiTabForm.TabPages[1];
        }

        //执行审核代码
        protected virtual void OnSys11()
        {
            
        }

        //弹窗查询
        protected virtual string doPOPWINDOS(string sTitle,string sFieldList,string sQuerySQL,string sQueryOrder,int sSELTYPE)
        {
            //弹窗选择
            string SELDATA = string.Empty; 
            FMPOPWINDOW myPOP = new FMPOPWINDOW();
            myPOP.uiTitle.Text = sTitle;
            myPOP.FieldList = sFieldList;
            myPOP.QuerySQL = sQuerySQL;
            myPOP.QueryOrder = sQueryOrder;
            myPOP.SELTYPE = sSELTYPE;
            myPOP.sUser = sUser;
            myPOP.sMenu = "P" + sMenu;
            myPOP.ShowDialog();
            SELDATA = myPOP.SELDATA;
            myPOP.Dispose();

            return SELDATA;
        }

        //表格设置代码
        protected virtual void OnGridDbClick(string tmpMenu, DataGridView tmpView)
        {
            //Console.WriteLine("当前用户:" + sUser);
            //if (sUser.ToLower() != "888888")
            //{
            //    return;
            //}
            FMSetGridView myGrid = new FMSetGridView();
            myGrid._UserId = sUser;
            myGrid.doLoadGrid(tmpMenu, tmpView);
            myGrid.ShowDialog();
            myGrid.Dispose();
            myGrid.Dispose();
        }

        public FMSINGLE()
        {
            InitializeComponent();
        }

        //界面初始化
        private void FMSINGLE_Load(object sender, EventArgs e)
        {
            uiTabForm.TabVisible = false;
            sys08.Visible = false;
            sys09.Visible = false;
        }

        //执行新增代码
        private void sys01_Click(object sender, EventArgs e)
        {
            OnSys01();
        }
        
        //执行编辑代码
        private void sys02_Click(object sender, EventArgs e)
        {
            OnSys02();
        }

        //执行查询代码
        private void sys04_Click(object sender, EventArgs e)
        {
            OnSys04();
        }

        //表格列设置统一控制
        private void uiDGView_DoubleClick(object sender, EventArgs e)
        {
            OnGridDbClick(sMenu,uiDGView);
        }

        //获得当前选中的行
        private void uiDGView_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            rowindex = e.RowIndex;
        }

        //执行保存代码
        private void sys08_Click(object sender, EventArgs e)
        {
            OnSys08();
        }

        //执行导入代码
        private void sys05_Click(object sender, EventArgs e)
        {
            OnSys05();

            OnSys04();
        }

        //控制按钮
        private void doSetButton(int iType)
        {
            //iType=1 返回新增+修改+浏览; =0  进入保存+取消
            if (iType == 0)
            {
                btnFirst.Visible = false;
                btnPrev.Visible = false;
                labPageIndex.Visible = false;
                btnNext.Visible = false;
                btnLast.Visible = false;
                toolStripSeparator1.Visible = false;

                if (sys01.Tag.ToString() == "0"){
                    sys01.Visible = false;
                }
                if (sys02.Tag.ToString() == "0")
                {
                    sys02.Visible = false;
                }
                if (sys03.Tag.ToString() == "0")
                {
                    sys03.Visible = false;
                }
                if (sys04.Tag.ToString() == "0")
                {
                    sys04.Visible = false;
                }
                if (sys05.Tag.ToString() == "0")
                {
                    sys05.Visible = false;
                }
                if (sys06.Tag.ToString() == "0")
                {
                    sys06.Visible = false;
                }
                if (sys07.Tag.ToString() == "0")
                {
                    sys07.Visible = false;
                }
                if (sys10.Tag.ToString() == "0")
                {
                    sys10.Visible = false;
                }
                if (sys11.Tag.ToString() == "0")
                {
                    sys11.Visible = false;
                }

                sys08.Visible = true;
                sys09.Visible = true;
            }

            if (iType == 1)
            {
                btnFirst.Visible = true;
                btnPrev.Visible = true;
                labPageIndex.Visible = true;
                btnNext.Visible = true;
                btnLast.Visible = true;
                toolStripSeparator1.Visible = true;

                if (sys01.Tag.ToString() == "0")
                {
                    sys01.Visible = true;
                }
                if (sys02.Tag.ToString() == "0")
                {
                    sys02.Visible = true;
                }
                if (sys03.Tag.ToString() == "0")
                {
                    sys03.Visible = true;
                }
                if (sys04.Tag.ToString() == "0")
                {
                    sys04.Visible = true;
                }
                if (sys05.Tag.ToString() == "0")
                {
                    sys05.Visible = true;
                }
                if (sys06.Tag.ToString() == "0")
                {
                    sys06.Visible = true;
                }
                if (sys07.Tag.ToString() == "0")
                {
                    sys07.Visible = true;
                }
                if (sys10.Tag.ToString() == "0")
                {
                    sys10.Visible = true;
                }
                if (sys11.Tag.ToString() == "0")
                {
                    sys11.Visible = true;
                }

                sys08.Visible = false;
                sys09.Visible = false;
            }

        }

        private void sys09_Click(object sender, EventArgs e)
        {
            OnSys09();
        }

        private void sys07_Click(object sender, EventArgs e)
        {
            OnSys07();
        }

        private void sys11_Click(object sender, EventArgs e)
        {
            OnSys11();
        }

        private void sys10_Click(object sender, EventArgs e)
        {
            OnSys10();
        }

        private void sys06_Click(object sender, EventArgs e)
        {
            OnSys06();
        }

        private void sys03_Click(object sender, EventArgs e)
        {
            OnSys03();
        }
    }

}
